﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using KPIS.DBM;
using KPIS.GERP.SERVICE.Model;

namespace KPIS.GERP.SERVICE.DAL
{
    public class Absence
    {
        Employee employee = new Employee();
        Common common = new Common();
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"]);

        #region Select

        public List<hrm_ps_absence> GetHrmPsAbsence(string absence_seq, string absence_type_seq, string emp_seq, string bgm_year
            , bool isGetHrmPsAbsenceType, bool isGetHrmPsEmployees)
        {
            DataTable dtb;
            var list = new List<hrm_ps_absence>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@absence_seq", absence_seq);
                dbManager.AddParameters(2, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(3, "@emp_seq", emp_seq);
                dbManager.AddParameters(4, "@bgm_year", bgm_year);

                string strSQL = "select * from hrm_ps_absence where record_status <> @delete_status"
                    + (!string.IsNullOrWhiteSpace(absence_seq) ? " and absence_seq = @absence_seq" : "")
                    + (!string.IsNullOrWhiteSpace(absence_type_seq) ? " and absence_type_seq = @absence_type_seq" : "")
                    + (!string.IsNullOrWhiteSpace(emp_seq) ? " and emp_seq = @emp_seq" : "")
                    + (!string.IsNullOrWhiteSpace(bgm_year) ? " and bgm_year = @bgm_year" : "")
                    ;

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            List<hrm_ps_absence_type> absenceTypeList = GetHrmPsAbsenceType(null);
            List<hrm_ps_employees> employeeList = employee.GetHrmPsEmployee(null, null, null, null, null, null, null,
                                                                            null, false, false, false, false, false,
                                                                            false, false, false, false, false, false,
                                                                            false, false, false);
            foreach (DataRow dr in dtb.Rows)
            {
                int? drAbsenceTypeSeq = common.ConvertToNullableInt(dr["absence_type_seq"].ToString());
                int? drEmployeeSeq = common.ConvertToNullableInt(dr["emp_seq"].ToString());

                hrm_ps_absence_type absentType = null;
                if (isGetHrmPsAbsenceType && drAbsenceTypeSeq.HasValue)
                {
                    absentType = (from at in absenceTypeList
                                  where at.absence_type_seq == drAbsenceTypeSeq
                                  select at).FirstOrDefault();
                }

                hrm_ps_employees absence_employee = null;
                if (isGetHrmPsEmployees && drEmployeeSeq.HasValue)
                {
                    absence_employee = (from employeees in employeeList
                                        where employeees.emp_seq == drEmployeeSeq
                                        select employeees).FirstOrDefault();
                }

                list.Add(new hrm_ps_absence
                {
                    absence_seq = Convert.ToInt32(dr["absence_seq"]),
                    absence_type_seq = common.ConvertToNullableInt(dr["absence_type_seq"]),
                    bgm_year = common.ConvertToNullableInt(dr["bgm_year"]),
                    start_date = common.ConvertToNullableDateTime(dr["start_date"]),
                    start_time = dr["start_time"].ToString(),
                    end_date = common.ConvertToNullableDateTime(dr["end_date"]),
                    end_time = dr["end_time"].ToString(),
                    day_count = common.ConvertToNullableInt(common.ConvertToNullableDouble(dr["day_count"])),
                    contact_address = dr["contact_address"].ToString(),
                    remarks = dr["remarks"].ToString(),
                    record_status = dr["record_status"].ToString(),
                    created_by = common.ConvertToNullableInt(dr["created_by"]),
                    created_when = common.ConvertToNullableDateTime(dr["created_when"]),
                    updated_by = common.ConvertToNullableInt(dr["updated_by"]),
                    updated_when = common.ConvertToNullableDateTime(dr["updated_when"]),
                    description = dr["description"].ToString(),
                    hrm_ps_absence_type = absentType,
                    hrm_ps_employees = absence_employee,
                    emp_seq = common.ConvertToNullableInt(dr["emp_seq"]),
                    request_title = Convert.ToString(dr["request_title"])
                });
            }

            return list;
        }

        public List<summary_hrm_ps_absence> GetSummaryHrmPsAbsence(string absence_type_seq, string emp_seq, string bgm_year)
        {
            List<summary_hrm_ps_absence> list;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@approval_status", Common.ApprovedStatus);
                dbManager.AddParameters(1, "@process_status", Common.ProcessStatus);
                dbManager.AddParameters(2, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(3, "@emp_seq", emp_seq);
                dbManager.AddParameters(4, "@bgm_year", bgm_year);
                dbManager.AddParameters(5, "@cancel_status", Common.CancelStatus);

                string strSelect = "";

                if (!string.IsNullOrWhiteSpace(absence_type_seq))
                {
                    strSelect += "absence_type_seq";
                }
                if (!string.IsNullOrWhiteSpace(emp_seq))
                {
                    strSelect += ", emp_seq";
                }
                if (!string.IsNullOrWhiteSpace(bgm_year))
                {
                    strSelect += ", bgm_year";
                }
                if (strSelect.StartsWith(", "))
                {
                    strSelect = strSelect.Substring(2);
                }

                string strSQL = "select " + strSelect
                    + ", sum(coalesce(day_count, 0)) as day_count"
                    + ", count(*) as time_count"
                    + ", max(end_date) as recent_absence_date"
                    + " from hrm_ps_absence where (record_status = @approval_status or record_status = @process_status)"

                    + (!string.IsNullOrWhiteSpace(absence_type_seq) ? " and absence_type_seq = @absence_type_seq" : "")
                    + (!string.IsNullOrWhiteSpace(emp_seq) ? " and emp_seq = @emp_seq" : "")
                    + (!string.IsNullOrWhiteSpace(bgm_year) ? " and bgm_year = @bgm_year" : "")

                    + " group by " + strSelect
                    ;

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<summary_hrm_ps_absence>)CollectionHelper.ConvertTo<summary_hrm_ps_absence>(dtb);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }

        public List<hrm_ps_absence_type> GetHrmPsAbsenceType(string absence_type_seq)
        {
            List<hrm_ps_absence_type> list = new List<hrm_ps_absence_type>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", Common.DeletedStatus);
                dbManager.AddParameters(1, "@absence_type_seq", absence_type_seq);

                string strSQL = "select * from hrm_ps_absence_types where record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(absence_type_seq) ? " and absence_type_seq = @absence_type_seq" : "")
                    ;

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                foreach (DataRow dr in dtb.Rows)
                {
                    list.Add(new hrm_ps_absence_type()
                    {
                        absence_type_seq = Convert.ToInt32(dr["absence_type_seq"].ToString()),
                        absence_type_name = dr["absence_type_name"].ToString(),
                        remarks = dr["remarks"].ToString(),
                        record_status = dr["record_status"].ToString(),
                        created_by = common.ConvertToNullableInt(dr["created_by"].ToString()),
                        created_when = common.ConvertToNullableDateTime(dr["created_when"].ToString()),
                        updated_by = common.ConvertToNullableInt(dr["updated_by"].ToString()),
                        updated_when = common.ConvertToNullableDateTime(dr["updated_when"].ToString())
                    });
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return list;
        }
        #endregion

        #region Insert / Update / Delete
        public string InsertHrmPsAbsence(string absence_type_seq
            , string bgm_year, string emp_seq
            , Nullable<DateTime> start_date, string start_time
            , Nullable<DateTime> end_date, string end_time
            , string day_count, string contact_address
            , string remarks, string record_status, string created_by, string description
            , string request_title)
        {
            string strSeq = null;
            description = string.IsNullOrWhiteSpace(description) ? description : description.Substring(0, description.Length > 250 ? 250 : description.Length);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@absence_seq", null);
                dbManager.AddParameters(1, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@emp_seq", emp_seq);
                dbManager.AddParameters(4, "@start_date", start_date);
                dbManager.AddParameters(5, "@start_time", start_time);
                dbManager.AddParameters(6, "@end_date", end_date);
                dbManager.AddParameters(7, "@end_time", end_time);
                dbManager.AddParameters(8, "@day_count", day_count);
                dbManager.AddParameters(9, "@contact_address", contact_address);
                dbManager.AddParameters(10, "@remarks", remarks);
                dbManager.AddParameters(11, "@record_status", record_status);
                dbManager.AddParameters(12, "@created_by", created_by);
                dbManager.AddParameters(13, "@created_when", DateTime.Now);
                dbManager.AddParameters(14, "@description", description);
                dbManager.AddParameters(15, "@request_title", request_title);

                string strSQL =
@"insert into hrm_ps_absence
(absence_type_seq, bgm_year, emp_seq
, start_date, start_time, end_date, end_time
, day_count, contact_address
, remarks, record_status, created_by, created_when
, description, request_title)
values(@absence_type_seq, @bgm_year, @emp_seq
, @start_date, @start_time, @end_date, @end_time
, @day_count, @contact_address
, @remarks, @record_status, @created_by, @created_when
, @description, @request_title)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = @"select last_insert_id() from hrm_ps_absence";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    strSeq = dtb.Rows[0][0].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return strSeq;
        }

        public void UpdateHrmPsAbsence(string absence_seq, string absence_type_seq
            , string bgm_year, string emp_seq
            , Nullable<DateTime> start_date, string start_time
            , Nullable<DateTime> end_date, string end_time
            , string day_count, string contact_address
            , string remarks, string record_status, string updated_by,string description
            , string request_title)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@absence_seq", absence_seq);
                dbManager.AddParameters(1, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@emp_seq", emp_seq);
                dbManager.AddParameters(4, "@start_date", start_date);
                dbManager.AddParameters(5, "@start_time", start_time);
                dbManager.AddParameters(6, "@end_date", end_date);
                dbManager.AddParameters(7, "@end_time", end_time);
                dbManager.AddParameters(8, "@day_count", day_count);
                dbManager.AddParameters(9, "@contact_address", contact_address);
                dbManager.AddParameters(10, "@remarks", remarks);
                dbManager.AddParameters(11, "@record_status", record_status);
                dbManager.AddParameters(12, "@updated_by", updated_by);
                dbManager.AddParameters(13, "@updated_when", DateTime.Now);
                dbManager.AddParameters(14, "@description", description);
                dbManager.AddParameters(15, "@request_title", request_title);

                string strSQL =
@"update hrm_ps_absence
set absence_type_seq = @absence_type_seq
, bgm_year = @bgm_year
, emp_seq = @emp_seq
, start_date = @start_date
, start_time = @start_time
, end_date = @end_date
, end_time = @end_time
, day_count = @day_count
, contact_address = @contact_address
, remarks = @remarks
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when 
, description = @description 
, request_title = @request_title 
where absence_seq = @absence_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

        public void UpdateStatusHrmPsAbsence(string absence_seq, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@absence_seq", absence_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
                                @"update hrm_ps_absence
                                set record_status = @record_status
                                , updated_by = @updated_by
                                , updated_when = @updated_when
                                where absence_seq = @absence_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

        public string InsertHrmPsAbsenceType(string absence_type_name, string remarks
            , string record_status, string created_by)
        {
            string strSeq = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@absence_type_seq", null);
                dbManager.AddParameters(1, "@absence_type_name", absence_type_name);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", record_status);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Now);

                string strSQL =
@"insert into hrm_ps_absence_type
(absence_type_name, remarks
, record_status, created_by, created_when)
values(@absence_type_name, @remarks
, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = @"select last_insert_id() from hrm_ps_absence_types";
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    strSeq = dtb.Rows[0][0].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }

            return strSeq;
        }

        public void UpdateHrmPsAbsenceType(string absence_type_seq, string absence_type_name
            , string remarks, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(1, "@absence_type_name", absence_type_name);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", record_status);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);

                string strSQL =
@"update hrm_ps_absence_types
set absence_type_name = @absence_type_name
, remarks = @remarks
, record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where absence_type_seq = @absence_type_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }

        public void UpdateStatusHrmPsAbsenceType(string absence_type_seq, string record_status, string updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@absence_type_seq", absence_type_seq);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                string strSQL =
@"update hrm_ps_absence_types
set record_status = @record_status
, updated_by = @updated_by
, updated_when = @updated_when
where absence_type_seq = @absence_type_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) {  dbManager.Connection.Close(); dbManager.Dispose();  }
            }
        }
        #endregion
    }
}